{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center><h1>第六章 连接</h1></center>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 一、关系型连接\n",
    "### 1. 连接的基本概念\n",
    "\n",
    "把两张相关的表按照某一个或某一组键连接起来是一种常见操作，\n",
    "\n",
    "在关系型连接中:\n",
    "\n",
    "$\\color{red}{键}$是十分重要的，往往用`on`参数表示。\n",
    "\n",
    "另一个重要的要素是**连接的形式**\n",
    "\n",
    "在`pandas`中的关系型连接函数`merge`和`join`:\n",
    "\n",
    "提供了`how`参数来代表连接形式\n",
    "> 分为左连接`left`\n",
    ">\n",
    "> 右连接`right`: 右表出现的扩展\n",
    ">\n",
    "> 内连接`inner`：同时出现的\n",
    ">\n",
    "> 外连接`outer`：全连接\n",
    "\n",
    "![图片1](https://www.jianshu.com/p/23a2f0804b82)\n",
    "\n",
    "\n",
    "若出现重复的键应该如何处理：\n",
    "> 只要两边同时出现的值，就以笛卡尔积的方式加入\n",
    ">\n",
    "> 如果单边出现则根据连接形式进行处理\n",
    "\n",
    "笛卡尔积：设左表中键`张三`出现两次，右表中的`张三`也出现两次，那么逐个进行匹配，最后产生的表必然包含`2*2`个姓名为`张三`的行。如图：\n",
    "\n",
    "![figure 2](https://www.jianshu.com/p/23a2f0804b82)\n",
    "\n",
    "其中左连接和右连接是等价的，由于它们的结果中的键是被一侧的表确定的，需要区分时是当需要方向性地添加到目标表\n",
    "\n",
    "### 2. 值连接\n",
    "\n",
    "区分于根据某一列的值来连接,可以通过几列值的组合进行连接，这种**基于值的连接**在`pandas`中可以由`merge`函数实现，例如第一张图的左连接："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Name  Age\n",
      "0  San Zhang   20\n",
      "1      Si Li   30\n",
      "\n",
      "      Name Gender\n",
      "0    Si Li      F\n",
      "1  Wu Wang      M\n",
      "--------------\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>30</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Name  Age Gender\n",
       "0  San Zhang   20    NaN\n",
       "1      Si Li   30      F"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'Name':['San Zhang','Si Li'], 'Age':[20,30]})\n",
    "df2 = pd.DataFrame({'Name':['Si Li','Wu Wang'], 'Gender':['F','M']})\n",
    "print(df1)\n",
    "print()\n",
    "print(df2)\n",
    "print('--------------')\n",
    "df1.merge(df2, on='Name', how='left')  # 左连接"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "当想要连接的列不具备相同的列名，可以通过`left_on`和`right_on`指定："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    df1_name  Age\n",
      "0  San Zhang   20\n",
      "1      Si Li   30\n",
      "\n",
      "  df2_name Gender\n",
      "0    Si Li      F\n",
      "1  Wu Wang      M\n",
      "------------\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>df1_name</th>\n",
       "      <th>Age</th>\n",
       "      <th>df2_name</th>\n",
       "      <th>Gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>30</td>\n",
       "      <td>Si Li</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    df1_name  Age df2_name Gender\n",
       "0  San Zhang   20      NaN    NaN\n",
       "1      Si Li   30    Si Li      F"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'df1_name':['San Zhang','Si Li'], 'Age':[20,30]})\n",
    "df2 = pd.DataFrame({'df2_name':['Si Li','Wu Wang'], 'Gender':['F','M']})\n",
    "print(df1)\n",
    "print()\n",
    "print(df2)\n",
    "print('------------')\n",
    "df1.merge(df2, left_on='df1_name', right_on='df2_name', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "如果两个表中的列出现了重复的列名，那么可以通过`suffixes`参数分别指定列名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Name  Grade\n",
      "0  San Zhang     70\n",
      "\n",
      "        Name  Grade\n",
      "0  San Zhang     80\n",
      "------------\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Grade_Chinese</th>\n",
       "      <th>Grade_Math</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>70</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Name  Grade_Chinese  Grade_Math\n",
       "0  San Zhang             70          80"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'Name':['San Zhang'],'Grade':[70]})\n",
    "df2 = pd.DataFrame({'Name':['San Zhang'],'Grade':[80]})\n",
    "print(df1)\n",
    "print()\n",
    "print(df2)\n",
    "print('------------')\n",
    "df1.merge(df2, on='Name', how='left', suffixes=['_Chinese','_Math'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "考虑出现重复元素：例如两位同学来自不同的班级，但是姓名相同，这种时候就要指定`on`参数为多个列使得正确连接："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Name  Age Class\n",
      "0  San Zhang   20   one\n",
      "1  San Zhang   21   two\n",
      "\n",
      "        Name Gender Class\n",
      "0  San Zhang      F   two\n",
      "1  San Zhang      M   one\n",
      "\n",
      "----- wrong ---------\n",
      "         Name  Age Class_x Gender Class_y\n",
      "0  San Zhang   20     one      F     two\n",
      "1  San Zhang   20     one      M     one\n",
      "2  San Zhang   21     two      F     two\n",
      "3  San Zhang   21     two      M     one \n",
      "\n",
      "----- right ---------\n",
      "         Name  Age Class Gender\n",
      "0  San Zhang   20   one      M\n",
      "1  San Zhang   21   two      F \n",
      "\n"
     ]
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'Name':['San Zhang', 'San Zhang'],\n",
    "                    'Age':[20, 21],\n",
    "                    'Class':['one', 'two']})\n",
    "df2 = pd.DataFrame({'Name':['San Zhang', 'San Zhang'],\n",
    "                    'Gender':['F', 'M'],\n",
    "                    'Class':['two', 'one']})\n",
    "print(df1)\n",
    "print()\n",
    "print(df2)\n",
    "print()\n",
    "tmp = df1.merge(df2, on='Name', how='left') \n",
    "print('----- wrong ---------\\n {} \\n'.format(tmp))\n",
    "tmp = df1.merge(df2, on=['Name', 'Class'], how='left') # 正确的结果\n",
    "print('----- right ---------\\n {} \\n'.format(tmp))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "进行基于唯一性的连接下，如果键不是唯一的，那么结果就会产生问题。当有几十万到上百万行的进行合并时，如果想要保证唯一性\n",
    "\n",
    "1. 除了用`duplicated`检查\n",
    "\n",
    "2. `merge`中也提供的`validate`检查唯一性模式。共有三种模式：\n",
    "\n",
    "> `1:1`:左右表的键都是唯一\n",
    ">\n",
    "> `1:m`：左表键唯一和\n",
    ">\n",
    "> `m:1`：右表键唯一。\n",
    "\n",
    "#### 【练一练】\n",
    "上面以多列为键的例子中，错误写法显然是一种多对多连接，而正确写法是一对一连接，请修改原表，使得以多列为键的正确写法能够通过`validate='1:m'`的检验，但不能通过`validate='m:1'`的检验。\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   no name  age\n",
      "0  11  Tom   13\n",
      "1  12  Jim   13\n",
      "2  13  Jim   14\n",
      "\n",
      "   no  weight\n",
      "0  11      30\n",
      "1  12      40\n",
      "2  13      40\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>no</th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11</td>\n",
       "      <td>Tom</td>\n",
       "      <td>13</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>Jim</td>\n",
       "      <td>13</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13</td>\n",
       "      <td>Jim</td>\n",
       "      <td>14</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   no name  age  weight\n",
       "0  11  Tom   13      30\n",
       "1  12  Jim   13      40\n",
       "2  13  Jim   14      40"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 实例5.2：正确应用 - 指定唯一值\n",
    "df1 = pd.DataFrame({'no':[11,12,13],'name' : ['Tom','Jim','Jim'], 'age' : [13, 13,14]})\n",
    "df2 = pd.DataFrame({'no' : [11,12,13], 'weight': [30,40,40]})\n",
    "print(df1)\n",
    "print()\n",
    "print(df2)\n",
    "df1.merge(df2, on='no', how='outer', validate=\"1:1\")#no为唯一值\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Name  Age Class\n",
      "0  San Zhang   20   one\n",
      "1  San Zhang   21   two\n",
      "\n",
      "        Name Gender Class\n",
      "0  San Zhang      F   one\n",
      "1  San Zhang      M   one\n",
      "--------- 1:m ------------\n",
      "        Name  Age Class Gender\n",
      "0  San Zhang   20   one      F\n",
      "1  San Zhang   20   one      M\n",
      "2  San Zhang   21   two    NaN\n",
      "\n",
      "不能通过说明 df2 不是 unique ，我以为是呢\n",
      "Merge keys are not unique in right dataset; not a many-to-one merge\n"
     ]
    }
   ],
   "source": [
    "#　要求：　多列为键的正确写法\n",
    "#        能通过validate='1:m'的检验 --> df1 的列 unique\n",
    "#        不能通过validate='m:1'的检验 --> df2 的列 disunique\n",
    "df1 = pd.DataFrame({'Name':['San Zhang', 'San Zhang'],\n",
    "                    'Age':[20, 21],\n",
    "                    'Class':['one', 'two']})\n",
    "df2 = pd.DataFrame({'Name':['San Zhang', 'San Zhang'],\n",
    "                    'Gender':['F', 'M'],\n",
    "                    'Class':['one', 'one']})  # disunique\n",
    "print(df1)\n",
    "print()\n",
    "print(df2)\n",
    "tmp = df1.merge(df2, on=['Name', 'Class'], how='left', validate='1:m')\n",
    "print('--------- 1:m ------------\\n{}\\n'.format(tmp))  # 能通过说明 df1 -- unique\n",
    "\n",
    "try:\n",
    "    tmp = df1.merge(df2, on=['Name', 'Class'], how='left', validate='m:1')\n",
    "except Exception as e:\n",
    "    print('不能通过说明 df2 不是 unique ，我以为是呢')\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. 索引连接\n",
    "\n",
    "把索引当作键 **与** 值连接本质上没有区别\n",
    "\n",
    ">`pandas` -- `join`: 索引连接:参数更少\n",
    ">\n",
    ">`on`： 索引名，单层索引时省略参数 --> 按照当前索引连接\n",
    ">\n",
    ">`how`：\n",
    ">\n",
    ">`lsuffix` -- `rsuffix`: 对重复的列指定左右后缀"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           Age\n",
      "Name          \n",
      "San Zhang   20\n",
      "Si Li       30\n",
      "\n",
      "        Gender\n",
      "Name          \n",
      "Si Li        F\n",
      "Wu Wang      M\n",
      "------------\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Age</th>\n",
       "      <th>Gender</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>San Zhang</th>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Si Li</th>\n",
       "      <td>30</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Age Gender\n",
       "Name                 \n",
       "San Zhang   20    NaN\n",
       "Si Li       30      F"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'Age':[20,30]}, index=pd.Series(['San Zhang','Si Li'],name='Name'))\n",
    "df2 = pd.DataFrame({'Gender':['F','M']}, index=pd.Series(['Si Li','Wu Wang'],name='Name'))\n",
    "print(df1)\n",
    "print()\n",
    "print(df2)\n",
    "print('------------')\n",
    "df1.join(df2, how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "仿照第2小节的例子，写出语文和数学分数合并的`join`版本："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           Grade\n",
      "Name            \n",
      "San Zhang     70\n",
      "\n",
      "           Grade\n",
      "Name            \n",
      "San Zhang     80\n",
      "------------\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Grade_Chinese</th>\n",
       "      <th>Grade_Math</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>San Zhang</th>\n",
       "      <td>70</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Grade_Chinese  Grade_Math\n",
       "Name                                \n",
       "San Zhang             70          80"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'Grade':[70]}, \n",
    "                   index=pd.Series(['San Zhang'], \n",
    "                   name='Name'))\n",
    "df2 = pd.DataFrame({'Grade':[80]}, \n",
    "                   index=pd.Series(['San Zhang'], \n",
    "                   name='Name'))\n",
    "print(df1)\n",
    "print()\n",
    "print(df2)\n",
    "print('------------')\n",
    "df1.join(df2, how='left', lsuffix='_Chinese', rsuffix='_Math')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对比`merge`中以多列为键的操作，`join`需要使用多级索引\n",
    "\n",
    "例如在`merge`中的最后一个例子可以如下写出："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                 Age\n",
      "Name      Class     \n",
      "San Zhang one     20\n",
      "          two     21\n",
      "\n",
      "                Gender\n",
      "Name      Class       \n",
      "San Zhang two        F\n",
      "          one        M\n",
      "------------\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Age</th>\n",
       "      <th>Gender</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name</th>\n",
       "      <th>Class</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">San Zhang</th>\n",
       "      <th>one</th>\n",
       "      <td>20</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>21</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Age Gender\n",
       "Name      Class            \n",
       "San Zhang one     20      M\n",
       "          two     21      F"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'Age':[20,21]}, \n",
    "                   index=pd.MultiIndex.from_arrays([['San Zhang', 'San Zhang'],\n",
    "                                                    ['one', 'two']], \n",
    "                   names=('Name','Class')))\n",
    "df2 = pd.DataFrame({'Gender':['F', 'M']}, \n",
    "                   index=pd.MultiIndex.from_arrays([['San Zhang', 'San Zhang'],\n",
    "                                                    ['two', 'one']], \n",
    "                   names=('Name','Class')))\n",
    "print(df1)\n",
    "print()\n",
    "print(df2)\n",
    "print('------------')\n",
    "df1.join(df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 二、方向连接\n",
    "### 1. concat\n",
    "关系型连接，其中最重要的参数是`on`和`how`\n",
    "\n",
    "以哪一列为键来合并有时候没那么重要 -- 》 只是希望**把两个表或者多个表按照纵向或者横向拼接** --- `pandas`中提供了`concat`函数来实现。\n",
    "\n",
    "在`concat`中，最常用参数:\n",
    "> `axis`: 拼接方向， 默认为0，纵向样本拼接；1--横向拼接字段、特征\n",
    ">\n",
    "> `join`: 连接形式\n",
    "> \n",
    "> `keys`: 新表中指示来自于哪一张旧表的名字\n",
    ">\n",
    "> Attention == 此 join 与彼 join 无关\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Name  Age\n",
      "0  San Zhang   20\n",
      "1      Si Li   30\n",
      "\n",
      "      Name  Age\n",
      "0  Wu Wang   40\n",
      "------------\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Wu Wang</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Name  Age\n",
       "0  San Zhang   20\n",
       "1      Si Li   30\n",
       "0    Wu Wang   40"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 纵向\n",
    "df1 = pd.DataFrame({'Name':['San Zhang','Si Li'], 'Age':[20,30]})\n",
    "df2 = pd.DataFrame({'Name':['Wu Wang'], 'Age':[40]})\n",
    "\n",
    "print(df1)\n",
    "print()\n",
    "print(df2)\n",
    "print('------------')\n",
    "pd.concat([df1, df2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Name  Age\n",
      "0  San Zhang   20\n",
      "1      Si Li   30\n",
      "\n",
      "   Grade\n",
      "0     80\n",
      "1     90\n",
      "\n",
      "  Gender\n",
      "0      M\n",
      "1      F\n",
      "------------\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Grade</th>\n",
       "      <th>Gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20</td>\n",
       "      <td>80</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>30</td>\n",
       "      <td>90</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Name  Age  Grade Gender\n",
       "0  San Zhang   20     80      M\n",
       "1      Si Li   30     90      F"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 横向\n",
    "df2 = pd.DataFrame({'Grade':[80, 90]})\n",
    "df3 = pd.DataFrame({'Gender':['M', 'F']})\n",
    "print(df1)\n",
    "print()\n",
    "print(df2)\n",
    "print()\n",
    "print(df3)\n",
    "\n",
    "print('------------')\n",
    "pd.concat([df1, df2, df3], 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    ":`concat`是处理关系型合并，但仍关于索引进行连接\n",
    "\n",
    "纵向拼接：列索引对齐:\n",
    "\n",
    "> 默认`join=outer` --> (全连接)表示保留所有的列，并将不存在的值设为缺失；\n",
    "> \n",
    "> `join=inner` --> 表示保留两个表都出现过的列\n",
    "\n",
    "横向拼接：行索引对齐\n",
    ">`join`参数同上"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Name  Age\n",
      "0  San Zhang   20\n",
      "1      Si Li   30\n",
      "\n",
      "      Name Gender\n",
      "0  Wu Wang      M\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Wu Wang</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Name   Age Gender\n",
       "0  San Zhang  20.0    NaN\n",
       "1      Si Li  30.0    NaN\n",
       "0    Wu Wang   NaN      M"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.DataFrame({'Name':['Wu Wang'], 'Gender':['M']})\n",
    "print(df1)\n",
    "print()\n",
    "print(df2)\n",
    "pd.concat([df1, df2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Name  Age\n",
      "0  San Zhang   20\n",
      "1      Si Li   30\n",
      "\n",
      "   Grade\n",
      "1     80\n",
      "2     90\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Grade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>30.0</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Name   Age  Grade\n",
       "0  San Zhang  20.0    NaN\n",
       "1      Si Li  30.0   80.0\n",
       "2        NaN   NaN   90.0"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.DataFrame({'Grade':[80, 90]}, index=[1, 2])\n",
    "print(df1)\n",
    "print()\n",
    "print(df2)\n",
    "pd.concat([df1, df2], 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Name  Age\n",
      "0  San Zhang   20\n",
      "1      Si Li   30\n",
      "\n",
      "   Grade\n",
      "1     80\n",
      "2     90\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Grade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>30</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Name  Age  Grade\n",
       "1  Si Li   30     80"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df1)\n",
    "print()\n",
    "print(df2)\n",
    "pd.concat([df1, df2], axis=1, join='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对使用多表直接的方向合并（尤其是横向的合并）：\n",
    "\n",
    "先用 `reset_index` 方法恢复默认整数索引再进行合并\n",
    "\n",
    "防止**索引的误对齐** 和 **重复索引的笛卡尔积**\n",
    "\n",
    "`keys`参数的使用场景: 多个表合并,且用户仍然想要知道新表中的数据来自于哪个原表，这时使用`keys`参数产生多级索引进行标记\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Name  Age\n",
      "0  San Zhang   20\n",
      "1      Si Li   21\n",
      "\n",
      "      Name  Age\n",
      "0  Wu Wang   21\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">one</th>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <th>0</th>\n",
       "      <td>Wu Wang</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Name  Age\n",
       "one 0  San Zhang   20\n",
       "    1      Si Li   21\n",
       "two 0    Wu Wang   21"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 例如，第一个表中都是一班的同学，而第二个表中都是二班的同学：df1 = pd.DataFrame({'Name':['San Zhang','Si Li'], 'Age':[20,21]})\n",
    "df2 = pd.DataFrame({'Name':['Wu Wang'],'Age':[21]})\n",
    "print(df1)\n",
    "print()\n",
    "print(df2)\n",
    "pd.concat([df1, df2], keys=['one', 'two'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. 序列与表的合并\n",
    "> `append`:序列追加到表的行末：\n",
    ">\n",
    "> ---- 对默认整数序列的索引 -- `ignore_index=True`对新序列对应索引自动标号，否则必须对`Series`指定`name`属性。\n",
    "> \n",
    ">\n",
    "> `assign`:序列追加到表的列末"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Name    Wu Wang\n",
      "Age          21\n",
      "dtype: object\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Wu Wang</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Name  Age\n",
       "0  San Zhang   20\n",
       "1      Si Li   21\n",
       "2    Wu Wang   21"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = pd.Series(['Wu Wang', 21], index = df1.columns)\n",
    "print(s)\n",
    "df1.append(s, ignore_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`assign`添加列 == `df['new_col'] = ...`\n",
    "\n",
    "但是`[]`会直接在原表上进行改动，而`assign`返回临时副本："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Name  Age  Grade\n",
      "0  San Zhang   20     80\n",
      "1      Si Li   21     90\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0    80\n",
       "1    90\n",
       "dtype: int64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = pd.Series([80, 90])\n",
    "print(df1.assign(Grade=s))\n",
    "s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Grade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>21</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Name  Age  Grade\n",
       "0  San Zhang   20     80\n",
       "1      Si Li   21     90"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1['Grade'] = s\n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 三、类连接操作\n",
    "`pandas`: 对两个表进行\"某些操作\"，这里把它们统称为类连接操作。\n",
    "\n",
    "### 1. 比较\n",
    "\n",
    "`compare`是在`1.1.0`后引入：比较两个表或者序列的不同处并将其汇总展示："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Name  Age  Class\n",
      "0  San Zhang   20    one\n",
      "1      Si Li   21    two\n",
      "2    Wu Wang   21  three\n",
      "\n",
      "        Name  Age  Class\n",
      "0  San Zhang   20    one\n",
      "1      Li Si   21    two\n",
      "2    Wu Wang   21  Three\n",
      "------------\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">Name</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Class</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>self</th>\n",
       "      <th>other</th>\n",
       "      <th>self</th>\n",
       "      <th>other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>Li Si</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>three</td>\n",
       "      <td>Three</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Name         Class       \n",
       "    self  other   self  other\n",
       "1  Si Li  Li Si    NaN    NaN\n",
       "2    NaN    NaN  three  Three"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'Name':['San Zhang', 'Si Li', 'Wu Wang'],\n",
    "                    'Age':[20, 21 ,21],\n",
    "                    'Class':['one', 'two', 'three']})\n",
    "df2 = pd.DataFrame({'Name':['San Zhang', 'Li Si', 'Wu Wang'],\n",
    "                    'Age':[20, 21 ,21],\n",
    "                    'Class':['one', 'two', 'Three']})\n",
    "print(df1)\n",
    "print()\n",
    "print(df2)\n",
    "print('------------')\n",
    "df1.compare(df2)  # 完全一样的只有 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对返回的不同行，若相同则会被填充为缺失值`NaN`\n",
    "> `other`： 传入的参数表\n",
    ">\n",
    "> `self`： 被调用的表自身。\n",
    "\n",
    "若需要完整显示表中所有元素比较情况，可以设置`keep_shape=True`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">Name</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Age</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Class</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>self</th>\n",
       "      <th>other</th>\n",
       "      <th>self</th>\n",
       "      <th>other</th>\n",
       "      <th>self</th>\n",
       "      <th>other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>Li Si</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>three</td>\n",
       "      <td>Three</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Name         Age        Class       \n",
       "    self  other self other   self  other\n",
       "0    NaN    NaN  NaN   NaN    NaN    NaN\n",
       "1  Si Li  Li Si  NaN   NaN    NaN    NaN\n",
       "2    NaN    NaN  NaN   NaN  three  Three"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.compare(df2, keep_shape=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. 组合\n",
    "\n",
    "`combine`： 按照一定的规则进行组合，自动进行列索引的对齐\n",
    "\n",
    "依次传入的列是两个表列名的并集`Series`\n",
    "\n",
    "如下：进行`A`列比较的时候，`s2`指代的就是一个全空的序列，因为它在被调用的表中并不存在，并且来自第一个表的序列索引会被`reindex`成两个索引的并集。具体可以尝试 print\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   A  B  C\n",
      "0  1  3  5\n",
      "1  2  4  6\n",
      "\n",
      "   B  C   D\n",
      "1  5  7   9\n",
      "2  6  8  10\n",
      "------------\n",
      " --- \n",
      "0    1.0\n",
      "1    2.0\n",
      "2    NaN\n",
      "Name: A, dtype: float64 0   NaN\n",
      "1   NaN\n",
      "2   NaN\n",
      "Name: A, dtype: float64 0   NaN\n",
      "1   NaN\n",
      "2   NaN\n",
      "Name: A, dtype: float64\n",
      " --- \n",
      "0    3.0\n",
      "1    4.0\n",
      "2    NaN\n",
      "Name: B, dtype: float64 0    NaN\n",
      "1    5.0\n",
      "2    6.0\n",
      "Name: B, dtype: float64 0    NaN\n",
      "1    4.0\n",
      "2    NaN\n",
      "Name: B, dtype: float64\n",
      " --- \n",
      "0    5.0\n",
      "1    6.0\n",
      "2    NaN\n",
      "Name: C, dtype: float64 0    NaN\n",
      "1    7.0\n",
      "2    8.0\n",
      "Name: C, dtype: float64 0    NaN\n",
      "1    6.0\n",
      "2    NaN\n",
      "Name: C, dtype: float64\n",
      " --- \n",
      "0   NaN\n",
      "1   NaN\n",
      "2   NaN\n",
      "Name: D, dtype: float64 0     NaN\n",
      "1     9.0\n",
      "2    10.0\n",
      "Name: D, dtype: float64 0   NaN\n",
      "1   NaN\n",
      "2   NaN\n",
      "Name: D, dtype: float64\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A    B    C   D\n",
       "0 NaN  NaN  NaN NaN\n",
       "1 NaN  4.0  6.0 NaN\n",
       "2 NaN  NaN  NaN NaN"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def choose_min(s1, s2):\n",
    "    s2 = s2.reindex_like(s1)\n",
    "    res = s1.where(s1<s2, s2)\n",
    "    res = res.mask(s1.isna()) # isna表示是否为缺失值，返回布尔序列\n",
    "    print(' --- ')\n",
    "    print(s1, s2, res)\n",
    "    return res\n",
    "\n",
    "df1 = pd.DataFrame({'A':[1,2], 'B':[3,4], 'C':[5,6]})\n",
    "df2 = pd.DataFrame({'B':[5,6], 'C':[7,8], 'D':[9,10]}, index=[1,2])\n",
    "print(df1)\n",
    "print()\n",
    "print(df2)\n",
    "print('------------')\n",
    "df1.combine(df2, choose_min)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 【练一练】\n",
    "请在上述代码的基础上修改，保留`df2`中4个未被`df1`替换的相应位置原始值。\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   A  B  C\n",
      "0  1  3  5\n",
      "1  2  4  6\n",
      "\n",
      "   B  C   D\n",
      "1  5  7   9\n",
      "2  6  8  10\n",
      "------------\n",
      " --- \n",
      "0    1.0\n",
      "1    2.0\n",
      "2    NaN\n",
      "Name: A, dtype: float64 0   NaN\n",
      "1   NaN\n",
      "2   NaN\n",
      "Name: A, dtype: float64 0   NaN\n",
      "1   NaN\n",
      "2   NaN\n",
      "Name: A, dtype: float64\n",
      " --- \n",
      "0    3.0\n",
      "1    4.0\n",
      "2    NaN\n",
      "Name: B, dtype: float64 0    NaN\n",
      "1    5.0\n",
      "2    6.0\n",
      "Name: B, dtype: float64 0    NaN\n",
      "1    4.0\n",
      "2    6.0\n",
      "Name: B, dtype: float64\n",
      " --- \n",
      "0    5.0\n",
      "1    6.0\n",
      "2    NaN\n",
      "Name: C, dtype: float64 0    NaN\n",
      "1    7.0\n",
      "2    8.0\n",
      "Name: C, dtype: float64 0    NaN\n",
      "1    6.0\n",
      "2    8.0\n",
      "Name: C, dtype: float64\n",
      " --- \n",
      "0   NaN\n",
      "1   NaN\n",
      "2   NaN\n",
      "Name: D, dtype: float64 0     NaN\n",
      "1     9.0\n",
      "2    10.0\n",
      "Name: D, dtype: float64 0     NaN\n",
      "1     9.0\n",
      "2    10.0\n",
      "Name: D, dtype: float64\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A    B    C     D\n",
       "0 NaN  NaN  NaN   NaN\n",
       "1 NaN  4.0  6.0   9.0\n",
       "2 NaN  6.0  8.0  10.0"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def choose_min(s1, s2):\n",
    "    s2 = s2.reindex_like(s1)\n",
    "    res = s1.where(s1<s2, s2)\n",
    "    # res = res.mask(s1.isna()) # isna表示是否为缺失值，返回布尔序列\n",
    "    print(' --- ')\n",
    "    print(s1, s2, res)\n",
    "    return res\n",
    "\n",
    "df1 = pd.DataFrame({'A':[1,2], 'B':[3,4], 'C':[5,6]})\n",
    "df2 = pd.DataFrame({'B':[5,6], 'C':[7,8], 'D':[9,10]}, index=[1,2])\n",
    "print(df1)\n",
    "print()\n",
    "print(df2)\n",
    "print('------------')\n",
    "df1.combine(df2, choose_min)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "此外，设置`overtwrite`参数为`False`可以保留$\\color{red}{被调用表}$中未出现在传入的参数表中的列，而不会设置未缺失值："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A    B    C   D\n",
       "0  1.0  NaN  NaN NaN\n",
       "1  2.0  4.0  6.0 NaN\n",
       "2  NaN  NaN  NaN NaN"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.combine(df2, choose_min, overwrite=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 【练一练】\n",
    "除了`combine`之外，`pandas`中还有一个`combine_first`方法，其功能是在对两张表组合时，若第二张表中的值在第一张表中对应索引位置的值不是缺失状态，那么就使用第一张表的值填充。下面给出一个例子，请用`combine`函数完成相同的功能。\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   A    B\n",
      "0  1  3.0\n",
      "1  2  NaN\n",
      "\n",
      "   A  B\n",
      "1  5  7\n",
      "2  6  8\n",
      "------------\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A    B\n",
       "0  1.0  3.0\n",
       "1  2.0  7.0\n",
       "2  6.0  8.0"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'A':[1,2], 'B':[3,np.nan]})\n",
    "df2 = pd.DataFrame({'A':[5,6], 'B':[7,8]}, index=[1,2])\n",
    "print(df1)\n",
    "print()\n",
    "print(df2)\n",
    "print('------------')\n",
    "df1.combine_first(df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 四、练习\n",
    "### Ex1：美国疫情数据集\n",
    "\n",
    "现有美国4月12日至11月16日的疫情报表，请将`New York`的`Confirmed, Deaths, Recovered, Active`合并为一张表，索引为按如下方法生成的日期字符串序列："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['04-12-2020', '04-13-2020', '04-14-2020', '04-15-2020', '04-16-2020']"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "date = pd.date_range('20200412', '20201116').to_series()\n",
    "date = date.dt.month.astype('string').str.zfill(2) +'-'+ date.dt.day.astype('string').str.zfill(2) +'-'+ '2020'\n",
    "date = date.tolist()\n",
    "date[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Recovered</th>\n",
       "      <th>Active</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Province_State</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>New York</th>\n",
       "      <td>189033</td>\n",
       "      <td>9385</td>\n",
       "      <td>23887.0</td>\n",
       "      <td>179648</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Confirmed  Deaths  Recovered  Active\n",
       "Province_State                                      \n",
       "New York           189033    9385    23887.0  179648"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_csv(f'./data/us_report/{date[0]}.csv', index_col='Province_State')\n",
    "df = data.loc[['New York'],['Confirmed', 'Deaths', 'Recovered', 'Active']]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_NewYork(date):\n",
    "    data = pd.read_csv('./data/us_report/{}.csv'.float(date), index_col='Province_State')\n",
    "    return data.loc[['New York'],['Confirmed', 'Deaths', 'Recovered', 'Active']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Recovered</th>\n",
       "      <th>Active</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>04-12-2020</th>\n",
       "      <td>189033</td>\n",
       "      <td>9385</td>\n",
       "      <td>23887.0</td>\n",
       "      <td>179648.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>04-13-2020</th>\n",
       "      <td>195749</td>\n",
       "      <td>10058</td>\n",
       "      <td>23887.0</td>\n",
       "      <td>185691.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>04-14-2020</th>\n",
       "      <td>203020</td>\n",
       "      <td>10842</td>\n",
       "      <td>23887.0</td>\n",
       "      <td>192178.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>04-15-2020</th>\n",
       "      <td>214454</td>\n",
       "      <td>11617</td>\n",
       "      <td>23887.0</td>\n",
       "      <td>202837.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>04-16-2020</th>\n",
       "      <td>223691</td>\n",
       "      <td>14832</td>\n",
       "      <td>23887.0</td>\n",
       "      <td>208859.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11-12-2020</th>\n",
       "      <td>545762</td>\n",
       "      <td>33975</td>\n",
       "      <td>81198.0</td>\n",
       "      <td>430589.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11-13-2020</th>\n",
       "      <td>551163</td>\n",
       "      <td>33993</td>\n",
       "      <td>81390.0</td>\n",
       "      <td>435780.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11-14-2020</th>\n",
       "      <td>556551</td>\n",
       "      <td>34010</td>\n",
       "      <td>81585.0</td>\n",
       "      <td>440956.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11-15-2020</th>\n",
       "      <td>560200</td>\n",
       "      <td>34032</td>\n",
       "      <td>81788.0</td>\n",
       "      <td>444380.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11-16-2020</th>\n",
       "      <td>563690</td>\n",
       "      <td>34054</td>\n",
       "      <td>81908.0</td>\n",
       "      <td>447728.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>219 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Confirmed  Deaths  Recovered    Active\n",
       "04-12-2020     189033    9385    23887.0  179648.0\n",
       "04-13-2020     195749   10058    23887.0  185691.0\n",
       "04-14-2020     203020   10842    23887.0  192178.0\n",
       "04-15-2020     214454   11617    23887.0  202837.0\n",
       "04-16-2020     223691   14832    23887.0  208859.0\n",
       "...               ...     ...        ...       ...\n",
       "11-12-2020     545762   33975    81198.0  430589.0\n",
       "11-13-2020     551163   33993    81390.0  435780.0\n",
       "11-14-2020     556551   34010    81585.0  440956.0\n",
       "11-15-2020     560200   34032    81788.0  444380.0\n",
       "11-16-2020     563690   34054    81908.0  447728.0\n",
       "\n",
       "[219 rows x 4 columns]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.concat([get_NewYork(item) for item in date])\n",
    "df.index = date\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for dt in date[1:]:\n",
    "#     next_data = pd.read_csv(f'./data/us_report/{dt}.csv', index_col='Province_State')\n",
    "#     next_df = next_data.loc[['New York'],['Confirmed', 'Deaths', 'Recovered', 'Active']]\n",
    "#     df = df.append(next_df)\n",
    "# df.index = date\n",
    "# df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Ex2：实现join函数\n",
    "\n",
    "请实现带有`how`参数的`join`函数\n",
    "\n",
    "* 假设连接的两表无公共列\n",
    "* 调用方式为 `join(df1, df2, how=\"left\")`\n",
    "* 给出测试样例"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
